﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class LabEquipmentService
    {
        /// <summary>
		/// 增加一条数据
		/// </summary>
        public static int Add(Model.LabEquipment model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into LabEquipment(");
            strSql.Append("Name,type,seller,buydate,price,quality,functions,LabID,status,propertyNO,usetype,count,manager,brand,model,images,handlerID)");
            strSql.Append(" values (");
            strSql.Append("@Name,@type,@seller,@buydate,@price,@quality,@functions,@LabID,@status,@propertyNO,@usetype,@count,@manager,@brand,@model,@images,@handlerID)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@type", SqlDbType.Int,4),
					new SqlParameter("@seller", SqlDbType.VarChar,100),
					new SqlParameter("@buydate", SqlDbType.DateTime),
					new SqlParameter("@price", SqlDbType.Money,8),
					new SqlParameter("@quality", SqlDbType.VarChar,100),
					new SqlParameter("@functions", SqlDbType.VarChar,100),
					new SqlParameter("@LabID", SqlDbType.Int,4),
					new SqlParameter("@status", SqlDbType.Int,4),
					new SqlParameter("@propertyNO", SqlDbType.VarChar,50),
					new SqlParameter("@usetype", SqlDbType.Int,4),
					new SqlParameter("@count", SqlDbType.Int,4),
					new SqlParameter("@manager", SqlDbType.VarChar,20),
					new SqlParameter("@brand", SqlDbType.VarChar,50),
					new SqlParameter("@model", SqlDbType.VarChar,50),
					new SqlParameter("@images", SqlDbType.VarChar,80),
					new SqlParameter("@handlerID", SqlDbType.Int,4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.type;
            parameters[2].Value = model.seller;
            parameters[3].Value = model.buydate;
            parameters[4].Value = model.price;
            parameters[5].Value = model.quality;
            parameters[6].Value = model.functions;
            parameters[7].Value = model.LabID;
            parameters[8].Value = model.status;
            parameters[9].Value = model.propertyNO;
            parameters[10].Value = model.usetype;
            parameters[11].Value = model.count;
            parameters[12].Value = model.manager;
            parameters[13].Value = model.brand;
            parameters[14].Value = model.model;
            parameters[15].Value = model.images;
            parameters[16].Value = model.handlerID;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        //根据条件列出设备
        public static List<Model.LabEquipment> GetLabEquipmentList(string strWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM LabEquipment");
            if (strWhere.Trim() != "")
            {
                sql.Append(" WHERE " + strWhere);
            }
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql.ToString());
            List<Model.LabEquipment> list = new List<Model.LabEquipment>();
            if (dr.HasRows)
            {
                Model.LabEquipment m;
                while (dr.Read())
                {
                    m = new Model.LabEquipment();
                    m.ID = int.Parse(dr[0].ToString());
                    m.Name = dr[1].ToString();
                    m.type = int.Parse(dr[2].ToString());
                    m.typeParam = DAL.ParameterService.SelectOne(m.type);
                    m.seller = dr[3].ToString();
                    if (dr[4].ToString() != "")
                    {
                        m.buydate = Convert.ToDateTime(dr[4]);
                    }
                    if (dr[5].ToString() != "")
                    {
                        m.price = decimal.Parse(dr[5].ToString());
                    }
                    m.quality = dr[6].ToString();
                    m.functions = dr[7].ToString();
                    m.LabID = int.Parse(dr[8].ToString());
                    m.Lab = DAL.LabService.SelectLabByID(m.LabID);
                    m.status = int.Parse(dr[9].ToString());
                    m.statusParam = DAL.ParameterService.SelectOne(m.status);
                    m.propertyNO = dr[10].ToString();
                    m.usetype = int.Parse(dr[11].ToString());
                    m.usetypeParam = DAL.ParameterService.SelectOne(m.usetype);
                    m.count = int.Parse(dr[12].ToString());
                    m.manager = dr[13].ToString();
                    list.Add(m);
                }
                dr.Close();
            }
            dr.Close();
            return list;
        }

        //根据ID查询一条设备
        public static Model.LabEquipment SelectLabEquipmentByID(int id)
        {
            string sql = string.Format("SELECT * FROM LabEquipment WHERE ID = {0}", id);
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            Model.LabEquipment LE = new Model.LabEquipment();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    LE = new Model.LabEquipment();
                    LE.ID = int.Parse(dr[0].ToString());
                    LE.Name = dr[1].ToString();
                    LE.type = int.Parse(dr[2].ToString());
                    LE.typeParam = DAL.ParameterService.SelectOne(LE.type);
                    LE.seller = dr[3].ToString();
                    if (dr[4].ToString() != "")
                    {
                        LE.buydate = Convert.ToDateTime(dr[4]);
                    }
                    if (dr[5].ToString() != "")
                    {
                        LE.price = decimal.Parse(dr[5].ToString());
                    }
                    LE.functions = dr[7].ToString();
                    LE.LabID = int.Parse(dr[8].ToString());
                    LE.Lab = DAL.LabService.SelectLabByID(LE.LabID);
                    LE.status = int.Parse(dr[9].ToString());
                    LE.statusParam = DAL.ParameterService.SelectOne(LE.status);
                    LE.propertyNO = dr[10].ToString();
                    LE.usetype = int.Parse(dr[11].ToString());
                    LE.usetypeParam = DAL.ParameterService.SelectOne(LE.usetype);
                    LE.count = int.Parse(dr[12].ToString());
                    LE.manager = dr[13].ToString();
                    LE.brand = dr[14].ToString();
                    LE.model = dr[15].ToString();
                    LE.images = dr[16].ToString();
                    if (dr[17].ToString() != "")
                    {
                        LE.handlerID = int.Parse(dr[17].ToString());
                    }
                }
                dr.Close();
            }
            dr.Close();
            return LE;
        }

        //根据pNO查询一条设备
        public static Model.LabEquipment SelectLabEquipmentBypNO(string pNO)
        {
            string sql = string.Format("SELECT * FROM LabEquipment WHERE propertyNO LIKE '%{0}%'", pNO);
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            Model.LabEquipment LE = new Model.LabEquipment();
            if (dr.HasRows)
            {
                if (dr.Read())
                {
                    LE = new Model.LabEquipment();
                    LE.ID = int.Parse(dr[0].ToString());
                    LE.Name = dr[1].ToString();
                    LE.type = int.Parse(dr[2].ToString());
                    LE.typeParam = DAL.ParameterService.SelectOne(LE.type);
                    LE.seller = dr[3].ToString();
                    if (dr[4].ToString() != "")
                    {
                        LE.buydate = Convert.ToDateTime(dr[4]);
                    }
                    if (dr[5].ToString() != "")
                    {
                        LE.price = decimal.Parse(dr[5].ToString());
                    }
                    LE.functions = dr[7].ToString();
                    LE.LabID = int.Parse(dr[8].ToString());
                    LE.Lab = DAL.LabService.SelectLabByID(LE.LabID);
                    LE.status = int.Parse(dr[9].ToString());
                    LE.statusParam = DAL.ParameterService.SelectOne(LE.status);
                    LE.propertyNO = dr[10].ToString();
                    LE.usetype = int.Parse(dr[11].ToString());
                    LE.usetypeParam = DAL.ParameterService.SelectOne(LE.usetype);
                    LE.count = int.Parse(dr[12].ToString());
                    LE.manager = dr[13].ToString();
                    LE.brand = dr[14].ToString();
                    LE.model = dr[15].ToString();
                    LE.images = dr[16].ToString();
                    if (dr[17].ToString() != "")
                    {
                        LE.handlerID = int.Parse(dr[17].ToString());
                    }
                }
                dr.Close();
            }
            dr.Close();
            return LE;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static bool Update(Model.LabEquipment model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update LabEquipment set ");
            strSql.Append("Name=@Name,");
            strSql.Append("type=@type,");
            strSql.Append("seller=@seller,");
            strSql.Append("buydate=@buydate,");
            strSql.Append("price=@price,");
            strSql.Append("quality=@quality,");
            strSql.Append("functions=@functions,");
            strSql.Append("LabID=@LabID,");
            strSql.Append("propertyNO=@propertyNO,");
            strSql.Append("usetype=@usetype,");
            strSql.Append("count=@count,");
            strSql.Append("manager=@manager,");
            strSql.Append("brand=@brand,");
            strSql.Append("model=@model,");
            strSql.Append("images=@images,");
            strSql.Append("handlerID=@handlerID");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@type", SqlDbType.Int,4),
					new SqlParameter("@seller", SqlDbType.VarChar,100),
					new SqlParameter("@buydate", SqlDbType.DateTime),
					new SqlParameter("@price", SqlDbType.Money,8),
					new SqlParameter("@quality", SqlDbType.VarChar,100),
					new SqlParameter("@functions", SqlDbType.VarChar,100),
					new SqlParameter("@LabID", SqlDbType.Int,4),
					new SqlParameter("@propertyNO", SqlDbType.VarChar,50),
					new SqlParameter("@usetype", SqlDbType.Int,4),
					new SqlParameter("@count", SqlDbType.Int,4),
					new SqlParameter("@manager", SqlDbType.VarChar,20),
					new SqlParameter("@brand", SqlDbType.VarChar,50),
					new SqlParameter("@model", SqlDbType.VarChar,50),
					new SqlParameter("@images", SqlDbType.VarChar,80),
					new SqlParameter("@handlerID", SqlDbType.Int,4),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.type;
            parameters[2].Value = model.seller;
            parameters[3].Value = model.buydate;
            parameters[4].Value = model.price;
            parameters[5].Value = model.quality;
            parameters[6].Value = model.functions;
            parameters[7].Value = model.LabID;
            parameters[8].Value = model.propertyNO;
            parameters[9].Value = model.usetype;
            parameters[10].Value = model.count;
            parameters[11].Value = model.manager;
            parameters[12].Value = model.brand;
            parameters[13].Value = model.model;
            parameters[14].Value = model.images;
            parameters[15].Value = model.handlerID;
            parameters[16].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //根据ID删除一条设备
        public static bool DelLabEquipmentByID(int id)
        {
            string sql = string.Format("DELETE FROM LabEquipment WHERE ID = {0}", id);
            int rows = SQLHelper.ExecuteNonQuery(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 增加一条报废数据
        /// </summary>
        public static bool EquipmentAbandon(Model.EquipmentAbandon model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into EquipmentAbandon(");
            strSql.Append("EID,checker,date,cause)");
            strSql.Append(" values (");
            strSql.Append("@EID,@checker,@date,@cause)");
            SqlParameter[] parameters = {
					new SqlParameter("@EID", SqlDbType.Int,4),
					new SqlParameter("@checker", SqlDbType.VarChar,50),
					new SqlParameter("@date", SqlDbType.DateTime),
					new SqlParameter("@cause", SqlDbType.VarChar,200)};
            parameters[0].Value = model.EID;
            parameters[1].Value = model.checker;
            parameters[2].Value = model.date;
            parameters[3].Value = model.cause;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //改变设备状态
        public static bool ChangeStatusByID(int id , string s)
        {
            int status;
            switch (s)
            {
                case "using":
                    status = 29;
                    break;
                case "fix":
                    status = 30;
                    break;
                case "aban":
                    status = 31;
                    break;
                case "wait":
                    status = 46;
                    break;
                case "rent":
                    status = 35;
                    break;
                default:
                    status = 39;
                    break;
            }
            string sql = string.Format("UPDATE LabEquipment SET status = {0} WHERE ID = {1}", status, id);
            int rows = DbHelperSQL.ExecuteSql(sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //增加一条借出数据
        public static int EquipmentRent(Model.EquipmentRent model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into EquipmentRent(");
            strSql.Append("EID,renter,renterTel,used,rentDate,timeDay,allower,returnDate,checker,isReturn)");
            strSql.Append(" values (");
            strSql.Append("@EID,@renter,@renterTel,@used,@rentDate,@timeDay,@allower,@returnDate,@checker,@isReturn)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@EID", SqlDbType.Int,4),
					new SqlParameter("@renter", SqlDbType.VarChar,50),
					new SqlParameter("@renterTel", SqlDbType.VarChar,50),
					new SqlParameter("@used", SqlDbType.VarChar,100),
					new SqlParameter("@rentDate", SqlDbType.DateTime),
					new SqlParameter("@timeDay", SqlDbType.Int,4),
					new SqlParameter("@allower", SqlDbType.VarChar,50),
					new SqlParameter("@returnDate", SqlDbType.DateTime),
					new SqlParameter("@checker", SqlDbType.VarChar,50),
					new SqlParameter("@isReturn", SqlDbType.Bit,1)};
            parameters[0].Value = model.EID;
            parameters[1].Value = model.renter;
            parameters[2].Value = model.renterTel;
            parameters[3].Value = model.used;
            parameters[4].Value = model.rentDate;
            parameters[5].Value = model.timeDay;
            parameters[6].Value = model.allower;
            parameters[7].Value = model.returnDate;
            parameters[8].Value = model.checker;
            parameters[9].Value = model.isReturn;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        //更新借出数据为归还
        public static bool EquipmentReturn(Model.EquipmentRent model)
        {
            string sql = string.Format("UPDATE EquipmentRent SET returnDate = '{0}',checker = '{1}',isReturn = 1 WHERE id = {2}",model.returnDate,model.checker,model.id);
            int rows = DbHelperSQL.ExecuteSql(sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //新增一条报修数据
        public static int EquipmentRepair(Model.EquipmentRepair model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into EquipmentRepair(");
            strSql.Append("EID,reportDate,allower,RepairType,RepairCause,budget,repairer,timeDay,cost,result,checker,returnDate,isReturn)");
            strSql.Append(" values (");
            strSql.Append("@EID,@reportDate,@allower,@RepairType,@RepairCause,@budget,@repairer,@timeDay,@cost,@result,@checker,@returnDate,@isReturn)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@EID", SqlDbType.Int,4),
					new SqlParameter("@reportDate", SqlDbType.DateTime),
					new SqlParameter("@allower", SqlDbType.VarChar,50),
					new SqlParameter("@RepairType", SqlDbType.Int,4),
					new SqlParameter("@RepairCause", SqlDbType.VarChar,200),
					new SqlParameter("@budget", SqlDbType.Float,8),
					new SqlParameter("@repairer", SqlDbType.VarChar,50),
					new SqlParameter("@timeDay", SqlDbType.Int,4),
					new SqlParameter("@cost", SqlDbType.Float,8),
					new SqlParameter("@result", SqlDbType.VarChar,50),
					new SqlParameter("@checker", SqlDbType.VarChar,50),
					new SqlParameter("@returnDate", SqlDbType.DateTime),
					new SqlParameter("@isReturn", SqlDbType.Bit,1)};
            parameters[0].Value = model.EID;
            parameters[1].Value = model.reportDate;
            parameters[2].Value = model.allower;
            parameters[3].Value = model.RepairType;
            parameters[4].Value = model.RepairCause;
            parameters[5].Value = model.budget;
            parameters[6].Value = model.repairer;
            parameters[7].Value = model.timeDay;
            parameters[8].Value = model.cost;
            parameters[9].Value = model.result;
            parameters[10].Value = model.checker;
            parameters[11].Value = model.returnDate;
            parameters[12].Value = model.isReturn;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        //更新报修数据为反馈
        public static bool EquipmentFixok(Model.EquipmentRepair model)
        {
            string sql = string.Format("UPDATE EquipmentRepair SET repairer = '{0}',timeDay = {1},cost = {2},result = '{3}',checker = '{4}',returnDate = '{5}',isReturn = 1 WHERE ID = {6}",model.repairer,model.timeDay,model.cost,model.result,model.checker,model.returnDate,model.ID);
            int rows = DbHelperSQL.ExecuteSql(sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
